﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Database.Classes;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void CreateLogTable()
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {

            conn.Open();
            using (SqlCommand cmd = new SqlCommand(@"CREATE TABLE [dbo].[Logs](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[DateTime] [datetime] NOT NULL,
	[Function] [nvarchar](50) COLLATE Polish_CI_AS NOT NULL,
	[Text] [nvarchar](2000) COLLATE Polish_CI_AS NOT NULL
) ON [PRIMARY]
", conn))
            {
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(cmd.CommandText, ex);
                }
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void CleanDB()
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
           
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(@"IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Planets_Players]') AND parent_object_id = OBJECT_ID(N'[dbo].[Planets]'))
ALTER TABLE [dbo].[Planets] DROP CONSTRAINT [FK_Planets_Players]
", conn))
            {
                try
                {
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fleets_Systems]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fleets]'))
ALTER TABLE [dbo].[Fleets] DROP CONSTRAINT [FK_Fleets_Systems]";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fleets_Systems1]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fleets]'))
ALTER TABLE [dbo].[Fleets] DROP CONSTRAINT [FK_Fleets_Systems1]";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Planets_Systems]') AND parent_object_id = OBJECT_ID(N'[dbo].[Planets]'))
ALTER TABLE [dbo].[Planets] DROP CONSTRAINT [FK_Planets_Systems]
";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"/****** Object:  Table [dbo].[Planets]    Script Date: 04/27/2008 14:55:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Planets]') AND type in (N'U'))
DROP TABLE [dbo].[Planets]";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Systems]') AND type in (N'U'))
DROP TABLE [dbo].[Systems]";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
CREATE TABLE [dbo].[Systems](
	[SystemID] [int] IDENTITY(1,1) NOT NULL,
	[SystemName] [nvarchar](50) COLLATE Polish_CI_AS NOT NULL,
	[X] [int] NOT NULL,
	[Y] [int] NOT NULL,
 CONSTRAINT [PK_Systems] PRIMARY KEY CLUSTERED 
(
	[SystemID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
CREATE TABLE [dbo].[Planets](
	[PlanetID] [int] NOT NULL,
	[SystemID] [int] NOT NULL,
	[OwnerID] [int] NOT NULL CONSTRAINT [DF_Planets_OwnerID]  DEFAULT ((0)),
	[FleetOwnerID] [int] NOT NULL CONSTRAINT [DF_Planets_FleetOwnerID]  DEFAULT ((0)),
	[ColonyShips] [float] NOT NULL CONSTRAINT [DF_Planets_ColonyShips]  DEFAULT ((0)),
	[Transports] [float] NOT NULL CONSTRAINT [DF_Planets_Transports]  DEFAULT ((0)),
	[Destroyers] [float] NOT NULL CONSTRAINT [DF_Planets_Destroyers]  DEFAULT ((0)),
	[Crusiers] [float] NOT NULL CONSTRAINT [DF_Planets_Crusiers]  DEFAULT ((0)),
	[Battleships] [float] NOT NULL CONSTRAINT [DF_Planets_Battleships]  DEFAULT ((0)),
	[Farms] [float] NOT NULL CONSTRAINT [DF_Planets_Farms]  DEFAULT ((0)),
	[Factories] [float] NOT NULL CONSTRAINT [DF_Planets_Factories]  DEFAULT ((0)),
	[Cybernets] [float] NOT NULL CONSTRAINT [DF_Planets_Cybernets]  DEFAULT ((0)),
	[Labs] [float] NOT NULL CONSTRAINT [DF_Planets_Labs]  DEFAULT ((0)),
	[Starbase] [float] NOT NULL CONSTRAINT [DF_Planets_Starbase]  DEFAULT ((0)),
	[PopulationLevel] [int] NOT NULL CONSTRAINT [DF_Planets_PopulationLevel]  DEFAULT ((0)),
	[PopToNextLevel] [float] NOT NULL CONSTRAINT [DF_Planets_PopToNextLevel]  DEFAULT ((0)),
	[ProductionPoints] [float] NOT NULL CONSTRAINT [DF_Planets_ProductionPoints]  DEFAULT ((0)),
 CONSTRAINT [PK_Planets] PRIMARY KEY CLUSTERED 
(
	[PlanetID] ASC,
	[SystemID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"
ALTER TABLE [dbo].[Planets]  WITH CHECK ADD  CONSTRAINT [FK_Planets_Players] FOREIGN KEY([OwnerID])
REFERENCES [dbo].[Players] ([PlayerId])";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"
ALTER TABLE [dbo].[Planets]  WITH CHECK ADD  CONSTRAINT [FK_Planets_Systems] FOREIGN KEY([SystemID])
REFERENCES [dbo].[Systems] ([SystemID])";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"ALTER TABLE [dbo].[Fleets]  WITH CHECK ADD  CONSTRAINT [FK_Fleets_Systems] FOREIGN KEY([FromSystem])
REFERENCES [dbo].[Systems] ([SystemID])";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"ALTER TABLE [dbo].[Fleets]  WITH CHECK ADD  CONSTRAINT [FK_Fleets_Systems1] FOREIGN KEY([ToSystem])
REFERENCES [dbo].[Systems] ([SystemID])";
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(cmd.CommandText + "\n\n" + ex.ToString(), ex);
                }
            }
        }
    }
};
